package com.liuzhilin.mapper;

public class DatabaseSQl {
    // 查询所有用户信息
    public static String userSql(){
        return "select * from users";
    }
    // 查询用户是否存在
    public static String userNameSql(){
        return "select * from users where username = ?";
    }
    //根据用户名查询用户(模糊查询)
    public static String userNameLikeSql(){
        return "select * from users where (username like CONCAT('%', ?, '%'))";
    }
    //注册新用户
    public static String userRegisterSql(){
        return "insert into users(userName,password,personalSignature,articleCount,birthday,status) values(?,?,?,0,?,8)";
    }
    //查询管理员
    public static String managerNameSql(){
        return "select * from managers where managerName = ?";
    }
    //查询所有文章
    public static String allArticleSql(){
        return "select * from articles";
    }
    //查询特定分区文章
    public static String articleSql(){
        return "select * from articles where category = ?";
    }
    //根据作者文章标题查询文章
    public static String singleArticleSql(){
        return "select * from articles where writer= ? and title= ? and reader is NULL";
    }
    //修改用户信息
    public static String modifyUserInformationSql(){
        return "update users set personalSignature = ?,birthday = ? where username = ?";
    }
    //根据作者，文章标题，文章区域查询文章,改变文章状态
    public static String passArticleSql(){
        return "update articles set status=1 where writer = ? and title = ? and category = ?";
    }
    //根据作者，文章标题，文章区域查询文章,删除文章
    public static String deleteArticleSql(){
        return "delete from articles where writer = ? and title = ? and category = ?";
    }
    //上传文章
    public static String uploadArticleSql(){
        //like是保留字要用反引号括起来
        return "insert into articles(writer,title,content,publishTime,category,status,`like`,`comment`,`favorite`)  values(?,?,?,?,?,0,0,0,0)";
    }
    //增加点赞，评论，收藏的数据
    public static String feedbackSql(){
        return "insert into articles(writer,title,content,publishTime,category,reader,status,`like`,`comment`,`favorite`) values(?,?,?,?,?,?,?,?,?,?)";
    }
    //更新点赞数，评论数，收藏数
    public static String updateFeedbackSql(){
        return "update articles set `like` = ?,`comment` = ?,`favorite` = ? where writer = ? and title = ? and category = ?";
    }
    //增加评论
    public static String uploadCommentSql(){
        return "insert into comments(writer,title,reader,comment)  values(?,?,?,?)";
    }
    //查询是否已经点赞收藏
    public static String selectFeedbackSql(){
        return "select * from articles where writer = ? and title = ? and reader = ? and status =?";
    }
    //查询评论
    public static String selectCommentSql(){
        return "select * from comments where writer = ? and title = ?";
    }
    //增加用户文章数量
    public static String updateUserArticleCountSql(){
        return "update users set articleCount = articleCount + 1 where username = ?";
    }
    //减少用户文章数量
    public static String updateUserArticleCountMinusSql(){
        return "update users set articleCount = articleCount - 1 where username = ?";
    }
    //查询某篇文章是否通过审核
    public static String selectArticleStatusSql(){
        return "select * from articles where writer = ? and title = ? and category = ? and status = 1";
    }
    //查询用户自己的文章
    public static String selectUserStatusArticleSql(){
        return "select * from articles where writer = ? and status = ?";
    }
    //查询用户浏览过的文章
    public static String selectUserProfileArticleSql(){
        return "select * from articles where reader = ? and status = ?";
    }
    //根据搜索字段查询文章
    public static String selectArticleByQuerySql(){
        return "SELECT * FROM articles where (title LIKE CONCAT('%', ?, '%') or content LIKE CONCAT('%', ?, '%') or writer LIKE CONCAT('%', ?, '%')) and status = 1";
    }
    //将文章设为未通过
    public static String notPassArticleSql(){
        return "update articles set status = 2 where writer = ? and title = ? and category = ?";
    }
    //更新用户状态（解封）
    public static String lockdownUserStatusSql(){
        return "update users set status = 8 where id = ?";
    }
    //更新用户状态（封禁）
    public static String disableUserStatusSql(){
        return "update users set status = ? where id = ?";
    }
    //查询未通过审核的文章
    public static String selectUnapprovedArticleSql(){
        return "select * from articles where status = 2 and writer = ? and reader is NULL";
    }
    //修改文章
    public static String modifyArticleSql(){
        return "update articles set content = ? where writer = ? and title = ? and category = ?";
    }
    //修改文章状态
    public static String modifyArticleStatusSql(){
        return "update articles set status = 0 where writer = ? and title = ? and category = ?";
    }
    //查询文章是否存在(测试时使用)
    public static String selectArticleSql(){
        return "select * from articles where writer = ? and title = ? and category = ?";
    }
    //记录管理员的操作
    public static String recordOperationLogSql(){
        return "insert into messages(sendMessage,receiveMessage,content,type,time) values(?,?,?,?,?)";
    }
    //根据用户id查询用户
    public static String selectUserByIdSql(){
        return "select * from users where id = ?";
    }
    //查询管理员操作日志
    public static String selectOperationLogSql(){
        return "select * from messages where type = ?";
    }
    //查询收到的信息
    public static String selectMessageSql(){
        return "select * from messages where receiveMessage = ? and type = ?";
    }
    //查询发出的信息
    public static String selectMessageByUserSql(){
        return "select * from messages where sendMessage = ?";
    }
    public static String getHistoricalMessagesSql() {
        return "SELECT * FROM messages WHERE ((sendMessage = ? AND receiveMessage = ?) OR (sendMessage = ? AND receiveMessage = ?)) AND type = 5 ";
    }
    //更新信息状态
    public static String updateMessageTypeSql(){
        return "update messages set type = 1 where receiveMessage = ? and content = ? and type =0";
    }
    //查询是否已经是好友了
    public static String selectFriendSql(){
        return "select * from friends where firstFriend = ? and secondFriend = ?";
    }
    //添加好友
    public static String addFriendSql(){
        return "insert into friends(firstFriend,secondFriend) values(?,?)";
    }
    //根据用户名查询朋友
    public static String selectFriendByNameSql(){
        return "select * from friends where firstFriend = ?";
    }
}
